package com.cjbdi.service;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.cjbdi.util.NumUtil;

/**
 * SfglgjljzsService
 */
@Service
@Transactional
public class SfglGjljzsService{
	@Autowired  
    @PersistenceContext  
    private EntityManager entityManager;// 注入EntityManager实例 
	@Autowired
	BaseService baseService;
	//干警廉洁指数概述（和去年同比） 
	/*public List getGsList(String kind,String type, String date, String fybm){ 
    	Query query = entityManager.createNativeQuery("select date,jbfy,fymc,cnum,pnum,result from sfzs_result_fzgj where type='"+type+"' and date='"+date+"' and kind='"+kind+"' and jbfy='"+fybm+"' order by date desc");  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }*/
	public List getGsList(String kind,String type, String date, String fybm){ 
    	Query query = entityManager.createNativeQuery("SELECT\r\n" + 
    			"	jbfy,\r\n" + 
    			"	fymc,\r\n" + 
    			"	SUM ( CASE WHEN sfzs_result_fzgj.DATE = '"+(Integer.parseInt(date)-1)+"' THEN RESULT ELSE 0 END ) AS lastyearzs,\r\n" + 
    			"	SUM ( CASE WHEN sfzs_result_fzgj.DATE = '"+date+"' THEN RESULT ELSE 0 END ) AS yearzs,\r\n" + 
    			"	SUM ( CASE WHEN sfzs_result_fzgj.DATE = '"+(Integer.parseInt(date)-1)+"' THEN pnum ELSE 0 END ) AS lastyeargj,\r\n" + 
    			"	SUM ( CASE WHEN sfzs_result_fzgj.DATE = '"+date+"' THEN pnum ELSE 0 END ) AS yeargj,\r\n" + 
    			"	SUM ( CASE WHEN sfzs_result_fzgj.DATE = '"+(Integer.parseInt(date)-1)+"' THEN cnum ELSE 0 END ) AS lastyearfz,\r\n" + 
    			"	SUM ( CASE WHEN sfzs_result_fzgj.DATE = '"+date+"' THEN cnum ELSE 0 END ) AS yearfz,\r\n" + 
    			"(\r\n" + 
    			"	SUM ( CASE WHEN sfzs_result_fzgj.DATE = '"+date+"' THEN RESULT ELSE 0 END ) - SUM ( CASE WHEN sfzs_result_fzgj.DATE = '"+(Integer.parseInt(date)-1)+"' THEN RESULT ELSE 0 END )) AS cz "+
    			"FROM\r\n" + 
    			"	sfzs_result_fzgj\r\n" + 
    			"WHERE\r\n" + 
    			"	jbfy ='"+fybm+"' \r\n" + 
    			"	AND TYPE = '"+type+"' \r\n" + 
    			"	AND DATE IN ( '"+date+"', '"+(Integer.parseInt(date)-1)+"' ) \r\n" + 
    			"	AND kind = '"+kind+"' \r\n" + 
    			"GROUP BY\r\n" + 
    			"	jbfy,\r\n" + 
    			"	fymc ");  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
	public List getFbZjList(String type, String date, String fybm){  
    	Query query = entityManager.createNativeQuery("select t1.fyjc,t1.cnum,t2.gj as pnum,t1.result from (select sfzs_czrk.fyjc,sfzs_czrk.fybm,(CASE WHEN cnum IS NULL THEN 0 ELSE cnum END ) AS cnum,(CASE WHEN pnum IS NULL THEN 0 ELSE pnum END ) AS pnum,(CASE WHEN result IS NULL THEN 0 ELSE result END ) AS result from sfzs_czrk left join sfzs_result_fzgj on  sfzs_czrk.fybm =sfzs_result_fzgj.jbfy and sfzs_result_fzgj.date=sfzs_czrk.year and kind='中' and type='"+type+"' where sfzs_czrk.year = '"+date+"' and sfzs_czrk.fybm LIKE'"+fybm.substring(0, 1)+"%' and sfzs_czrk.fybm<>'"+fybm.substring(0, 1)+"'||'00' AND substring(sfzs_czrk.fybm FROM 3 FOR 1 ) ='0' order by result)t1 join (select substring(fybm from 1 for 2) as fybm,count(*) as gj from sfzs_rsxx GROUP BY substring(fybm from 1 for 2))t2 on substring(t1.fybm from 1 for 2) = t2.fybm order by t1.result");  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList(); 
        for(int i=0;i<rows.size();i++) {
			Map row = (Map)rows.get(i);
			row.put("id", i+1);
			row.put("lv", NumUtil.toNxiaoshu(row.get("result").toString(), 2)+"%");
			
		}
		
        return rows;
    }
	public List getFbGjList(String type, String date, String fybm){  
    	Query query = entityManager.createNativeQuery("select t1.fyjc,t1.cnum,t2.gj as pnum,t1.result from (select sfzs_czrk.fyjc,sfzs_czrk.fybm,(CASE WHEN cnum IS NULL THEN 0 ELSE cnum END ) AS cnum,(CASE WHEN pnum IS NULL THEN 0 ELSE pnum END ) AS pnum,(CASE WHEN result IS NULL THEN 0 ELSE result END ) AS result from sfzs_czrk left join sfzs_result_fzgj on  sfzs_czrk.fybm =sfzs_result_fzgj.jbfy and sfzs_result_fzgj.date=sfzs_czrk.year AND kind='高' and type='"+type+"' where sfzs_czrk.year = '"+date+"' AND substring(sfzs_czrk.fybm FROM 2 FOR 2 ) ='00' order by result)t1 join (select substring(fybm from 1 for 1) as fybm,count(*) as gj from sfzs_rsxx GROUP BY substring(fybm from 1 for 1))t2 on substring(t1.fybm from 1 for 1) = t2.fybm order by t1.result");  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        for(int i=0;i<rows.size();i++) {
			Map row = (Map)rows.get(i);
			row.put("id", i+1);
			row.put("lv", NumUtil.toNxiaoshu(row.get("result").toString(), 2)+"%");
			
		}
        return rows;
    }
    //全国排名表格
    public List getQgTableList(String type, String date, boolean isQuanguo){  
    	String sql ="";
    	if(isQuanguo) {
    		if(type.equals("year")) {
    			sql = "SELECT jbfy,fymc as fy,\r\n" + 
            			"sum(case when sfzs_result_fzgj.date='"+(Integer.parseInt(date)-1)+"' then result else 0 end )as lastyearzs,\r\n" + 
            			"sum(case when sfzs_result_fzgj.date='"+date+"' then result else 0 end) as yearzs,\r\n" + 
            			"(sum(case when sfzs_result_fzgj.date='"+date+"' then result else 0 end) - sum(case when sfzs_result_fzgj.date='"+(Integer.parseInt(date)-1)+"' then result else 0 end )) as cz from sfzs_result_fzgj where fymc='全国' and type='"+type+"' and kind ='高' GROUP BY jbfy,fymc ORDER BY yearzs";
    		}else {
    			sql = "SELECT jbfy,fy,lastyearzs,hbzs,yearzs,(yearzs-lastyearzs) as cz,(yearzs-hbzs) as hbcz from(SELECT jbfy,fymc as fy,\r\n" + 
            			"sum(case when sfzs_result_fzgj.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then result else 0 end )as lastyearzs,\r\n" + 
            			"sum(case when sfzs_result_fzgj.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then result else 0 end) as hbzs,\r\n" + 
            			"sum(case when sfzs_result_fzgj.date='"+date+"' and type='"+type+"' then result else 0 end) as yearzs\r\n" + 
            			" from sfzs_result_fzgj where fymc='全国' and kind ='高' GROUP BY jbfy,fymc ORDER BY yearzs)t";
    		}
    	}else {
    		if(type.equals("year")) {
    			sql = "select c_fbdm as jbfy,fy,lastyearzs,yearzs,cz,rank() over(order by yearzs) as rank from(select c_fbdm,c_mc as fy,(CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,(CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs,(CASE WHEN cz IS NULL THEN 0 ELSE cz END ) AS cz from t_fypz left join (\r\n" + 
            			"SELECT jbfy,\r\n" + 
            			"sum(case when sfzs_result_fzgj.date='"+(Integer.parseInt(date)-1)+"' then result else 0 end )as lastyearzs,\r\n" + 
            			"sum(case when sfzs_result_fzgj.date='"+date+"' then result else 0 end) as yearzs,\r\n" + 
            			"(sum(case when sfzs_result_fzgj.date='"+date+"' then result else 0 end) - sum(case when sfzs_result_fzgj.date='"+(Integer.parseInt(date)-1)+"' then result else 0 end )) as cz from sfzs_result_fzgj where jbfy<>'X00' and fymc!='全国' and type='"+type+"' and kind ='高' GROUP BY jbfy,fymc)\r\n" + 
            			" t on t_fypz.c_fbdm = t.jbfy  where substring(t_fypz.c_fbdm from 2 for 2)='00' and t_fypz.c_fbdm<>'000' and t_fypz.c_fbdm<>'X00' and t_fypz.c_fbdm<>'W00' order by yearzs,lastyearzs,fy)t1";
    		}else {
    			sql = "select c_fbdm as jbfy,fy,lastyearzs,yearzs,(yearzs-lastyearzs) as cz,\r\n" + 
    					"	(yearzs-hbzs) as hbcz,rank() over(order by yearzs) as rank from(select c_fbdm,c_mc as fy,(CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,(CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs,(CASE WHEN hbzs IS NULL THEN 0 ELSE hbzs END ) AS hbzs from t_fypz left join (\r\n" + 
            			"SELECT jbfy,\r\n" + 
            			"sum(case when sfzs_result_fzgj.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then result else 0 end )as lastyearzs,\r\n" + 
            			"sum(case when sfzs_result_fzgj.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then result else 0 end) as hbzs,\r\n" + 
            			"sum(case when sfzs_result_fzgj.date='"+date+"' and type='"+type+"' then result else 0 end) as yearzs\r\n" + 
            			" from sfzs_result_fzgj where jbfy<>'X00' and fymc!='全国' and kind ='高' GROUP BY jbfy,fymc)\r\n" + 
            			" t on t_fypz.c_fbdm = t.jbfy  where substring(t_fypz.c_fbdm from 2 for 2)='00' and t_fypz.c_fbdm<>'000' and t_fypz.c_fbdm<>'X00' and t_fypz.c_fbdm<>'W00' order by yearzs,lastyearzs,fy)t1";
    		}
    	}
    	Query query = entityManager.createNativeQuery(sql);  
    	query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //中院排行
    public List getZjTableList(String type, String date, String fybm){ 
    	String sql = "";
    	if(type.equals("year")) {
    		sql = "select jbfy,fy,lastyearzs,yearzs,cz,rank() over(order by yearzs) as rank from(select sfzs_czrk.fybm as jbfy,fyjc as fy,(CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,(CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs,(CASE WHEN cz IS NULL THEN 0 ELSE cz END ) AS cz from sfzs_czrk left join " + 
        			"(	SELECT jbfy,fyjc as fy," + 
        			"sum(case when sfzs_result_fzgj.date='"+(Integer.parseInt(date)-1)+"' then result else 0 end )as lastyearzs," + 
        			"sum(case when sfzs_result_fzgj.date='"+date+"' then result else 0 end) as yearzs," + 
        			"(sum(case when sfzs_result_fzgj.date='"+date+"' then result else 0 end) - sum(case when sfzs_result_fzgj.date='"+(Integer.parseInt(date)-1)+"' then result else 0 end )) as cz from sfzs_result_fzgj,sfzs_czrk where sfzs_czrk.year = '"+date+"' and sfzs_result_fzgj.jbfy=sfzs_czrk.fybm and jbfy like'"+fybm.substring(0, 1)+"%' and type='"+type+"' and date in('"+date+"','"+(Integer.parseInt(date)-1)+"') and kind ='中' GROUP BY jbfy,fyjc" + 
        			") t on sfzs_czrk.fybm = t.jbfy where sfzs_czrk.year = '"+date+"' and sfzs_czrk.fybm LIKE'"+fybm.substring(0, 1)+"%' and sfzs_czrk.fybm<>'"+fybm.substring(0, 1)+"'||'00' and substring(sfzs_czrk.fybm from 3 for 1)='0' ORDER BY\r\n" + 
        			"	yearzs,lastyearzs,fy)t1";
    	}else {
    		sql = "select jbfy,fy,lastyearzs,hbzs,yearzs,(yearzs-lastyearzs) as cz,\r\n" + 
    				"	(yearzs-hbzs) as hbcz,rank() over(order by yearzs) as rank from(select sfzs_czrk.fybm as jbfy,fyjc as fy,(CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,( CASE WHEN hbzs IS NULL THEN 0 ELSE hbzs END ) AS hbzs,(CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs from sfzs_czrk left join " + 
        			"(	SELECT jbfy,fyjc as fy," + 
        			"sum(case when sfzs_result_fzgj.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then result else 0 end )as lastyearzs," + 
        			"sum(case when sfzs_result_fzgj.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then result else 0 end) as hbzs," +
        			"sum(case when sfzs_result_fzgj.date='"+date+"' and type='"+type+"' then result else 0 end) as yearzs" + 
        			" from sfzs_result_fzgj,sfzs_czrk where sfzs_czrk.year = '"+date+"' and sfzs_result_fzgj.jbfy=sfzs_czrk.fybm and jbfy like'"+fybm.substring(0, 1)+"%' and kind ='中' GROUP BY jbfy,fyjc" + 
        			") t on sfzs_czrk.fybm = t.jbfy where sfzs_czrk.year = '"+date+"' and sfzs_czrk.fybm LIKE'"+fybm.substring(0, 1)+"%' and sfzs_czrk.fybm<>'"+fybm.substring(0, 1)+"'||'00' and substring(sfzs_czrk.fybm from 3 for 1)='0' ORDER BY\r\n" + 
        			"	yearzs,lastyearzs,fy)t1";
    	}
    	
    	Query query = entityManager.createNativeQuery(sql);  
    	query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //直辖市中院排行
    public List getZjZxTableList(String type, String date, String fybm){ 
    	String sql = "";
    	if(type.equals("year")) {
    		sql = "select jbfy,fy,lastyearzs,yearzs,cz,rank() over(order by yearzs) as rank from(select sfzs_czrk.fybm as jbfy,fyjc as fy,(CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,(CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs,(CASE WHEN cz IS NULL THEN 0 ELSE cz END ) AS cz from sfzs_czrk left join " + 
        			"(	SELECT jbfy,fyjc as fy," + 
        			"sum(case when sfzs_result_fzgj.date='"+(Integer.parseInt(date)-1)+"' then result else 0 end )as lastyearzs," + 
        			"sum(case when sfzs_result_fzgj.date='"+date+"' then result else 0 end) as yearzs," + 
        			"(sum(case when sfzs_result_fzgj.date='"+date+"' then result else 0 end) - sum(case when sfzs_result_fzgj.date='"+(Integer.parseInt(date)-1)+"' then result else 0 end )) as cz from sfzs_result_fzgj,sfzs_czrk where sfzs_czrk.year = '"+date+"' and sfzs_result_fzgj.jbfy=sfzs_czrk.fybm and jbfy like'"+fybm.substring(0, 1)+"%' AND right(jbfy,1)='0' AND jbfy <>'"+fybm.substring(0, 1)+"00' and type='"+type+"' and date in('"+date+"','"+(Integer.parseInt(date)-1)+"') and kind ='辖' GROUP BY jbfy,fyjc" + 
        			") t on sfzs_czrk.fybm = t.jbfy where sfzs_czrk.year = '"+date+"' and sfzs_czrk.fybm LIKE'"+fybm.substring(0, 1)+"%' and sfzs_czrk.fybm<>'"+fybm.substring(0, 1)+"'||'00' and substring(sfzs_czrk.fybm from 3 for 1)='0' ORDER BY\r\n" + 
        			"	yearzs,lastyearzs,fy)t1";
    	}else {
    		sql = "select jbfy,fy,lastyearzs,hbzs,yearzs,(yearzs-lastyearzs) as cz,(yearzs-hbzs) as hbcz,rank() over(order by yearzs) as rank from(select sfzs_czrk.fybm as jbfy,fyjc as fy,(CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,(CASE WHEN hbzs IS NULL THEN 0 ELSE hbzs END ) AS hbzs,(CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs from sfzs_czrk left join " + 
        			"(	SELECT jbfy,fyjc as fy," + 
        			"sum(case when sfzs_result_fzgj.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then result else 0 end )as lastyearzs," + 
        			"sum(case when sfzs_result_fzgj.date='"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' then result else 0 end )as hbzs," + 
        			"sum(case when sfzs_result_fzgj.date='"+date+"' and type='"+type+"' then result else 0 end) as yearzs" + 
        			" from sfzs_result_fzgj,sfzs_czrk where sfzs_czrk.year = '"+date+"' and sfzs_result_fzgj.jbfy=sfzs_czrk.fybm and jbfy like'"+fybm.substring(0, 1)+"%' AND right(jbfy,1)='0' AND jbfy <>'"+fybm.substring(0, 1)+"00' and date in('"+date+"','"+(Integer.parseInt(date)-1)+"') and kind ='辖' GROUP BY jbfy,fyjc" + 
        			") t on sfzs_czrk.fybm = t.jbfy where sfzs_czrk.year = '"+date+"' and sfzs_czrk.fybm LIKE'"+fybm.substring(0, 1)+"%' and sfzs_czrk.fybm<>'"+fybm.substring(0, 1)+"'||'00' and substring(sfzs_czrk.fybm from 3 for 1)='0' ORDER BY\r\n" + 
        			"	yearzs,lastyearzs,fy)t1";
    	}
    	
    	Query query = entityManager.createNativeQuery(sql);  
    	query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //全国指数 
  	public List getQg(String type, String date, String fybm){ 
      	Query query = entityManager.createNativeQuery("select date,jbfy,fymc,cnum,pnum,result from sfzs_result_fzgj where type='"+type+"' and date ='"+date+"' and fymc='全国'");  
          query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
          List rows = query.getResultList();  
          return rows;
    }
    //干警数
  	public List getGj(String fybm){ 
      	Query query = entityManager.createNativeQuery("select count(*) as gj from sfzs_rsxx where fybm like'"+fybm+"%'");  
          query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
          List rows = query.getResultList();  
          return rows;
    }
    //辖区排名表格
    public List getXqTableList(String type, String date, String fybm){  
    	String sql = "";
    	if(type.equals("year")) {
    		sql = "SELECT\r\n" + 
        			"	replace(c_mc,'人民法院','法院') as fy,jbfy," + 
        			"	lastyearzs,\r\n" + 
        			"	yearzs,\r\n" + 
        			"	cz,\r\n" + 
        			"	rank ( ) over ( ORDER BY yearzs ) AS rank \r\n" + 
        			"FROM\r\n" + 
        			"	(\r\n" + 
        			"SELECT\r\n" + 
        			"	c_mc,t_fypz.c_fbdm as jbfy," + 
        			"	( CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,\r\n" + 
        			"	( CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs,\r\n" + 
        			"	( CASE WHEN cz IS NULL THEN 0 ELSE cz END ) AS cz \r\n" + 
        			"FROM\r\n" + 
        			"	t_fypz\r\n" + 
        			"	LEFT JOIN (\r\n" + 
        			"SELECT\r\n" + 
        			"	jbfy,\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+(Integer.parseInt(date)-1)+"' THEN result ELSE 0 END ) AS lastyearzs,\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+date+"' THEN result ELSE 0 END ) AS yearzs,\r\n" + 
        			"	(\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+date+"' THEN result ELSE 0 END ) - sum( CASE WHEN sfzs_result_fzgj.date = '"+(Integer.parseInt(date)-1)+"' THEN result ELSE 0 END ) \r\n" + 
        			"	) AS cz \r\n" + 
        			"FROM\r\n" + 
        			"	sfzs_result_fzgj\r\n" + 
        			"WHERE\r\n" + 
        			"	jbfy like'"+fybm.substring(0, 2)+"%'\r\n" + 
        			"	AND type = '"+type+"' \r\n" + 
        			"	AND date IN ( '"+date+"', '"+(Integer.parseInt(date)-1)+"' ) \r\n" + 
        			"	AND kind = '辖' \r\n" + 
        			"GROUP BY\r\n" + 
        			"	jbfy\r\n" + 
        			"	) t ON t_fypz.c_fbdm = t.jbfy \r\n" + 
        			"WHERE\r\n" + 
        			"	t_fypz.c_fbdm like'"+fybm.substring(0, 2)+"%' and t_fypz.c_fbdm<>'"+fybm.substring(0, 2)+"'||'0' and right(c_mc,5)<>'（已撤销）'" + 
        			"ORDER BY\r\n" + 
        			"	yearzs,\r\n" + 
        			"	lastyearzs,\r\n" + 
        			"	c_mc \r\n" + 
        			"	) t1";
    	}else {
    		sql = "SELECT\r\n" + 
        			"	replace(c_mc,'人民法院','法院') as fy,jbfy," + 
        			"	lastyearzs,\r\n" + 
        			"	hbzs,\r\n" + 
        			"	yearzs,\r\n" + 
        			"	( yearzs - lastyearzs ) AS cz,\r\n" + 
        			"	( yearzs - hbzs ) AS hbcz," + 
        			"	rank ( ) over ( ORDER BY yearzs ) AS rank \r\n" + 
        			"FROM\r\n" + 
        			"	(\r\n" + 
        			"SELECT\r\n" + 
        			"	c_mc,t_fypz.c_fbdm as jbfy," + 
        			"	( CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,\r\n" + 
        			"	( CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs,\r\n" + 
        			"	( CASE WHEN hbzs IS NULL THEN 0 ELSE hbzs END ) AS hbzs \r\n" + 
        			"FROM\r\n" + 
        			"	t_fypz\r\n" + 
        			"	LEFT JOIN (\r\n" + 
        			"SELECT\r\n" + 
        			"	jbfy,\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+(Integer.parseInt(date)-1)+"' and type='"+type+"' THEN result ELSE 0 END ) AS lastyearzs,\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' THEN result ELSE 0 END ) AS hbzs,\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+date+"' and type='"+type+"' THEN result ELSE 0 END ) AS yearzs\r\n" + 
        			"FROM\r\n" + 
        			"	sfzs_result_fzgj\r\n" + 
        			"WHERE\r\n" + 
        			"	jbfy like'"+fybm.substring(0, 2)+"%'\r\n" + 
        			"	AND date IN ( '"+date+"', '"+(Integer.parseInt(date)-1)+"' ) \r\n" + 
        			"	AND kind = '辖' \r\n" + 
        			"GROUP BY\r\n" + 
        			"	jbfy\r\n" + 
        			"	) t ON t_fypz.c_fbdm = t.jbfy \r\n" + 
        			"WHERE\r\n" + 
        			"	t_fypz.c_fbdm like'"+fybm.substring(0, 2)+"%' and t_fypz.c_fbdm<>'"+fybm.substring(0, 2)+"'||'0' and right(c_mc,5)<>'（已撤销）'" + 
        			"ORDER BY\r\n" + 
        			"	yearzs,\r\n" + 
        			"	lastyearzs,\r\n" + 
        			"	c_mc \r\n" + 
        			"	) t1";
    	}
    	
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //区县法院在所属高院辖区内排名
    public List getRankInGyxq(String type, String date, String fybm){  
    	String sql = "SELECT rank from (SELECT\r\n" + 
    			"	replace(c_mc,'人民法院','法院') as fy,jbfy," + 
    			"	lastyearzs,\r\n" + 
    			"	yearzs,\r\n" + 
    			"	cz,\r\n" + 
    			"	rank ( ) over ( ORDER BY yearzs ) AS rank \r\n" + 
    			"FROM\r\n" + 
    			"	(\r\n" + 
    			"SELECT\r\n" + 
    			"	c_mc,t_fypz.c_fbdm as jbfy," + 
    			"	( CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,\r\n" + 
    			"	( CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs,\r\n" + 
    			"	( CASE WHEN cz IS NULL THEN 0 ELSE cz END ) AS cz \r\n" + 
    			"FROM\r\n" + 
    			"	t_fypz\r\n" + 
    			"	LEFT JOIN (\r\n" + 
    			"SELECT\r\n" + 
    			"	jbfy,\r\n" + 
    			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+(Integer.parseInt(date)-1)+"' THEN result ELSE 0 END ) AS lastyearzs,\r\n" + 
    			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+date+"' THEN result ELSE 0 END ) AS yearzs,\r\n" + 
    			"	(\r\n" + 
    			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+date+"' THEN result ELSE 0 END ) - sum( CASE WHEN sfzs_result_fzgj.date = '"+(Integer.parseInt(date)-1)+"' THEN result ELSE 0 END ) \r\n" + 
    			"	) AS cz \r\n" + 
    			"FROM\r\n" + 
    			"	sfzs_result_fzgj\r\n" + 
    			"WHERE\r\n" + 
    			"	jbfy like'"+fybm.substring(0, 1)+"%'\r\n" + 
    			"	AND RIGHT ( jbfy, 1 ) <> '0'"+
    			"	AND type = '"+type+"' \r\n" + 
    			"	AND date IN ( '"+date+"', '"+(Integer.parseInt(date)-1)+"' ) \r\n" + 
    			"	AND kind = '辖' \r\n" + 
    			"GROUP BY\r\n" + 
    			"	jbfy\r\n" + 
    			"	) t ON t_fypz.c_fbdm = t.jbfy \r\n" + 
    			"WHERE\r\n" + 
    			"	t_fypz.c_fbdm like'"+fybm.substring(0, 1)+"%' and RIGHT ( c_fbdm, 1 ) <> '0' and right(c_mc,5)<>'（已撤销）'" + 
    			"ORDER BY\r\n" + 
    			"	yearzs,\r\n" + 
    			"	lastyearzs,\r\n" + 
    			"	c_mc \r\n" + 
    			"	) t1) t2 where jbfy='"+fybm+"'";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //附表表格（区县）
    public List getFbQxList(String type, String date, String fybm){  
    	String sql = "SELECT\r\n" + 
    			"	replace(t1.c_mc,'人民法院','法院') as fyjc,\r\n" + 
    			"	t1.cnum,\r\n" + 
    			"	t2.gj AS pnum,\r\n" + 
    			"	t1.RESULT \r\n" + 
    			"FROM\r\n" + 
    			"	(\r\n" + 
    			"SELECT\r\n" + 
    			"	t_fypz.c_mc,\r\n" + 
    			"	t_fypz.c_fbdm,\r\n" + 
    			"	( CASE WHEN cnum IS NULL THEN 0 ELSE cnum END ) AS cnum,\r\n" + 
    			"	( CASE WHEN pnum IS NULL THEN 0 ELSE pnum END ) AS pnum,\r\n" + 
    			"	( CASE WHEN RESULT IS NULL THEN 0 ELSE RESULT END ) AS RESULT \r\n" + 
    			"FROM\r\n" + 
    			"	t_fypz\r\n" + 
    			"	LEFT JOIN sfzs_result_fzgj ON t_fypz.c_fbdm = sfzs_result_fzgj.jbfy \r\n" + 
    			"	AND kind = '辖' " + 
    			"	AND date='"+date+"'\r\n" + 
    			"   AND type='"+type+"'\r\n" +
    			"WHERE\r\n" + 
    			"	c_fbdm LIKE'"+fybm.substring(0, 2)+"%' \r\n" + 
    			"	AND right(c_mc,5)<>'（已撤销）'"+
    			"ORDER BY\r\n" + 
    			"RESULT\r\n" + 
    			"	) t1\r\n" + 
    			"	JOIN (\r\n" + 
    			"SELECT fybm AS fybm,\r\n" + 
    			"	COUNT ( * ) AS gj \r\n" + 
    			"FROM\r\n" + 
    			"	sfzs_rsxx \r\n" + 
    			"GROUP BY\r\n" + 
    			"	fybm) t2 ON t1.c_fbdm = t2.fybm \r\n" + 
    			"ORDER BY\r\n" + 
    			"	t1.RESULT";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //附表表格（区县本身）
    public List getFbQxSelfList(String type, String date, String fybm){  
    	String sql = "SELECT\r\n" + 
    			"	replace(t1.c_mc,'人民法院','法院') as fyjc,\r\n" + 
    			"	t1.cnum,\r\n" + 
    			"	t2.gj AS pnum,\r\n" + 
    			"	t1.RESULT \r\n" + 
    			"FROM\r\n" + 
    			"	(\r\n" + 
    			"SELECT\r\n" + 
    			"	t_fypz.c_mc,\r\n" + 
    			"	t_fypz.c_fbdm,\r\n" + 
    			"	( CASE WHEN cnum IS NULL THEN 0 ELSE cnum END ) AS cnum,\r\n" + 
    			"	( CASE WHEN pnum IS NULL THEN 0 ELSE pnum END ) AS pnum,\r\n" + 
    			"	( CASE WHEN RESULT IS NULL THEN 0 ELSE RESULT END ) AS RESULT \r\n" + 
    			"FROM\r\n" + 
    			"	t_fypz\r\n" + 
    			"	LEFT JOIN sfzs_result_fzgj ON t_fypz.c_fbdm = sfzs_result_fzgj.jbfy \r\n" + 
    			"	AND kind = '辖' AND right(c_mc,5)<>'（已撤销）'" + 
    			"	AND date='"+date+"'\r\n" + 
    			"WHERE\r\n" + 
    			"	c_fbdm ='"+fybm +"'"+ 
    			"ORDER BY\r\n" + 
    			"RESULT\r\n" + 
    			"	) t1\r\n" + 
    			"	JOIN (\r\n" + 
    			"SELECT fybm AS fybm,\r\n" + 
    			"	COUNT ( * ) AS gj \r\n" + 
    			"FROM\r\n" + 
    			"	sfzs_rsxx \r\n" + 
    			"GROUP BY\r\n" + 
    			"	fybm) t2 ON t1.c_fbdm = t2.fybm \r\n" + 
    			"ORDER BY\r\n" + 
    			"	t1.RESULT";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //同等规模城市排名表格
    public List getTdZjTableList(String type, String date, String fybm){  
    	String sql = "";
    	if(type.equals("year")) {
    		sql = "SELECT jbfy," + 
        			"	fy,\r\n" + 
        			"	lastyearzs,\r\n" + 
        			"	yearzs,\r\n" + 
        			"	cz,\r\n" + 
        			"	rank ( ) over ( ORDER BY yearzs ) AS rank \r\n" + 
        			"FROM\r\n" + 
        			"	(\r\n" + 
        			"SELECT sfzs_czrk.fybm as jbfy," + 
        			"	replace(fyjc,'法院','')||'法院' AS fy,\r\n" + 
        			"	( CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,\r\n" + 
        			"	( CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs,\r\n" + 
        			"	( CASE WHEN cz IS NULL THEN 0 ELSE cz END ) AS cz \r\n" + 
        			"FROM\r\n" + 
        			"	sfzs_czrk\r\n" + 
        			"	LEFT JOIN (\r\n" + 
        			"SELECT\r\n" + 
        			"	jbfy,\r\n" + 
        			"	fyjc AS fy,\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+(Integer.parseInt(date)-1)+"' THEN result ELSE 0 END ) AS lastyearzs,\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+date+"' THEN result ELSE 0 END ) AS yearzs,\r\n" + 
        			"	(\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+date+"' THEN result ELSE 0 END ) - sum( CASE WHEN sfzs_result_fzgj.date = '"+(Integer.parseInt(date)-1)+"' THEN result ELSE 0 END ) \r\n" + 
        			"	) AS cz \r\n" + 
        			"FROM\r\n" + 
        			"	sfzs_result_fzgj,\r\n" + 
        			"	sfzs_czrk \r\n" + 
        			"WHERE\r\n" + 
        			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
        			"	AND sfzs_result_fzgj.jbfy = sfzs_czrk.fybm \r\n" + 
        			"	AND size in (select size from sfzs_czrk where sfzs_czrk.year='"+date+"' and fybm = '"+fybm+"') \r\n" + 
        			"	AND type = '"+type+"' \r\n" + 
        			"	AND date IN ( '"+date+"', '"+(Integer.parseInt(date)-1)+"' ) \r\n" + 
        			"	AND (kind = '中' or (kind='高' and fybm in('100','200','900','M00'))) \r\n" + 
        			"GROUP BY\r\n" + 
        			"	jbfy,\r\n" + 
        			"	fyjc \r\n" + 
        			"	) t ON sfzs_czrk.fybm = t.jbfy \r\n" + 
        			"WHERE\r\n" + 
        			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
        			"	AND size in (select size from sfzs_czrk where sfzs_czrk.year='"+date+"' and fybm = '"+fybm+"') \r\n" + 
        			"ORDER BY\r\n" + 
        			"	yearzs,\r\n" + 
        			"	lastyearzs,\r\n" + 
        			"	fy \r\n" + 
        			"	) t1";
    	}else {
    		sql = "SELECT jbfy,	fy,\r\n" + 
    				"	lastyearzs,\r\n" + 
    				"	yearzs,\r\n" + 
    				"	hbzs,\r\n" + 
    				"	(yearzs-lastyearzs) as cz,\r\n" + 
    				"	(yearzs-hbzs) as hbcz,\r\n" + 
    				"	rank ( ) over ( ORDER BY yearzs ) AS rank \r\n" + 
        			"FROM\r\n" + 
        			"	(\r\n" + 
        			"SELECT sfzs_czrk.fybm as jbfy," + 
        			"	replace(fyjc,'法院','')||'法院' AS fy,\r\n" + 
        			"	( CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,\r\n" + 
        			"	( CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs,\r\n" + 
        			"	( CASE WHEN hbzs IS NULL THEN 0 ELSE hbzs END ) AS hbzs \r\n" + 
        			"FROM\r\n" + 
        			"	sfzs_czrk\r\n" + 
        			"	LEFT JOIN (\r\n" + 
        			"SELECT\r\n" + 
        			"	jbfy,\r\n" + 
        			"	fyjc AS fy,\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+(Integer.parseInt(date)-1)+"' and type='"+type+"' THEN result ELSE 0 END ) AS lastyearzs,\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' THEN result ELSE 0 END ) AS hbzs,\r\n" +
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+date+"' and type='"+type+"' THEN result ELSE 0 END ) AS yearzs\r\n" + 
        			"FROM\r\n" + 
        			"	sfzs_result_fzgj,\r\n" + 
        			"	sfzs_czrk \r\n" + 
        			"WHERE\r\n" + 
        			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
        			"	AND sfzs_result_fzgj.jbfy = sfzs_czrk.fybm \r\n" + 
        			"	AND size in (select size from sfzs_czrk where sfzs_czrk.year='"+date+"' and fybm = '"+fybm+"') \r\n" +  
        			"	AND date IN ( '"+date+"', '"+(Integer.parseInt(date)-1)+"' ) \r\n" + 
        			"	AND (kind = '中' or (kind='高' and fybm in('100','200','900','M00'))) \r\n" + 
        			"GROUP BY\r\n" + 
        			"	jbfy,\r\n" + 
        			"	fyjc \r\n" + 
        			"	) t ON sfzs_czrk.fybm = t.jbfy \r\n" + 
        			"WHERE\r\n" + 
        			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
        			"	AND size in (select size from sfzs_czrk where sfzs_czrk.year='"+date+"' and fybm = '"+fybm+"') \r\n" + 
        			"ORDER BY\r\n" + 
        			"	yearzs,\r\n" + 
        			"	lastyearzs,\r\n" + 
        			"	fy \r\n" + 
        			"	) t1";
    	}
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //同等直辖市中院排名表格
    public List getTdZxTableList(String type, String date, String fybm){  
    	String sql = "";
    	if(type.equals("year")) {
    		sql = "SELECT jbfy," + 
        			"	fy,\r\n" + 
        			"	lastyearzs,\r\n" + 
        			"	yearzs,\r\n" + 
        			"	cz,\r\n" + 
        			"	rank ( ) over ( ORDER BY yearzs ) AS rank \r\n" + 
        			"FROM\r\n" + 
        			"	(\r\n" + 
        			"SELECT sfzs_czrk.fybm as jbfy," + 
        			"	fyjc AS fy,\r\n" + 
        			"	( CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,\r\n" + 
        			"	( CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs,\r\n" + 
        			"	( CASE WHEN cz IS NULL THEN 0 ELSE cz END ) AS cz \r\n" + 
        			"FROM\r\n" + 
        			"	sfzs_czrk\r\n" + 
        			"	LEFT JOIN (\r\n" + 
        			"SELECT\r\n" + 
        			"	jbfy,\r\n" + 
        			"	fyjc AS fy,\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+(Integer.parseInt(date)-1)+"' THEN result ELSE 0 END ) AS lastyearzs,\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+date+"' THEN result ELSE 0 END ) AS yearzs,\r\n" + 
        			"	(\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+date+"' THEN result ELSE 0 END ) - sum( CASE WHEN sfzs_result_fzgj.date = '"+(Integer.parseInt(date)-1)+"' THEN result ELSE 0 END ) \r\n" + 
        			"	) AS cz \r\n" + 
        			"FROM\r\n" + 
        			"	sfzs_result_fzgj,\r\n" + 
        			"	sfzs_czrk \r\n" + 
        			"WHERE\r\n" + 
        			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
        			"	AND sfzs_result_fzgj.jbfy = sfzs_czrk.fybm \r\n" + 
        			"	AND (substring(jbfy from 1 for 1)='1' or substring(jbfy from 1 for 1)='2' or substring(jbfy from 1 for 1)='9' or substring(jbfy from 1 for 1)='M')" + 
        			"	AND type = '"+type+"' \r\n" + 
        			"	AND date IN ( '"+date+"', '"+(Integer.parseInt(date)-1)+"' ) \r\n" + 
        			"	AND kind = '中' \r\n" + 
        			"GROUP BY\r\n" + 
        			"	jbfy,\r\n" + 
        			"	fyjc \r\n" + 
        			"	) t ON sfzs_czrk.fybm = t.jbfy \r\n" + 
        			"WHERE\r\n" + 
        			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
        			"	AND (fybm like'1%0' or fybm like'2%0' or fybm like'9%0' or fybm like'M%0') AND fybm not in('100','200','900','M00')" + 
        			"ORDER BY\r\n" + 
        			"	yearzs,\r\n" + 
        			"	lastyearzs,\r\n" + 
        			"	fy \r\n" + 
        			"	) t1";
    	}else {
    		sql = "select jbfy,fy,lastyearzs,hbzs,yearzs,(yearzs-lastyearzs) as cz,(yearzs-hbzs) as hbcz," + 
        			"	rank ( ) over ( ORDER BY yearzs ) AS rank \r\n" + 
        			"FROM\r\n" + 
        			"	(\r\n" + 
        			"SELECT sfzs_czrk.fybm as jbfy," + 
        			"	fyjc AS fy,\r\n" + 
        			"	( CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,\r\n" + 
        			"	( CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs,\r\n" + 
        			"	( CASE WHEN hbzs IS NULL THEN 0 ELSE hbzs END ) AS hbzs \r\n" + 
        			"FROM\r\n" + 
        			"	sfzs_czrk\r\n" + 
        			"	LEFT JOIN (\r\n" + 
        			"SELECT\r\n" + 
        			"	jbfy,\r\n" + 
        			"	fyjc AS fy,\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+(Integer.parseInt(date)-1)+"' and type='"+type+"' THEN result ELSE 0 END ) AS lastyearzs,\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' THEN result ELSE 0 END ) AS hbzs,\r\n" + 
        			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+date+"' and type='"+type+"' THEN result ELSE 0 END ) AS yearzs \r\n" + 
        			"FROM\r\n" + 
        			"	sfzs_result_fzgj,\r\n" + 
        			"	sfzs_czrk \r\n" + 
        			"WHERE\r\n" + 
        			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
        			"	AND sfzs_result_fzgj.jbfy = sfzs_czrk.fybm \r\n" + 
        			"	AND (substring(jbfy from 1 for 1)='1' or substring(jbfy from 1 for 1)='2' or substring(jbfy from 1 for 1)='9' or substring(jbfy from 1 for 1)='M')" + 
        			"	AND date IN ( '"+date+"', '"+(Integer.parseInt(date)-1)+"' ) \r\n" + 
        			"	AND kind = '中' \r\n" + 
        			"GROUP BY\r\n" + 
        			"	jbfy,\r\n" + 
        			"	fyjc \r\n" + 
        			"	) t ON sfzs_czrk.fybm = t.jbfy \r\n" + 
        			"WHERE\r\n" + 
        			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
        			"	AND (fybm like'1%0' or fybm like'2%0' or fybm like'9%0' or fybm like'M%0') AND fybm not in('100','200','900','M00')" + 
        			"ORDER BY\r\n" + 
        			"	yearzs,\r\n" + 
        			"	lastyearzs,\r\n" + 
        			"	fy \r\n" + 
        			"	) t1";
    	}
    	
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //同等收案量排名表格
    public List getTdSalTableList(String type, String date, String fybm){  
    	StringBuffer sql = new StringBuffer("SELECT\r\n" + 
    			"	sal,\r\n" + 
    			"	fybm,\r\n" + 
    			"	REPLACE ( fyjc, '法院', '' ) || '法院' AS fy,\r\n" + 
    			"	( CASE WHEN RESULT IS NULL THEN 0 ELSE RESULT END ) AS RESULT  \r\n" + 
    			"FROM\r\n" + 
    			"	sfzs_czrk\r\n" + 
    			"	LEFT JOIN sfzs_result_fzgj ON sfzs_result_fzgj.jbfy = sfzs_czrk.fybm \r\n" + 
    			"	AND sfzs_czrk.YEAR =  sfzs_result_fzgj.date\r\n" + 
    			" 	AND kind = '中' and type = '"+type+"' \r\n" + 
    			"WHERE\r\n" + 
    			"	sfzs_czrk.YEAR = '"+date+"'\r\n" + 
    			"	AND sal IN ( SELECT sal FROM sfzs_czrk WHERE sfzs_czrk.YEAR = '"+date+"' AND fybm = '"+fybm+"' ) 	\r\n" + 
    			"ORDER BY\r\n" + 
    			"	result");
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //区县法院基本信息
    public List getQxInfo(String date, String fybm, String type){  
    	String sql = "SELECT\r\n" + 
    			"	(\r\n" + 
    			"SELECT\r\n" + 
    			"	count( * ) \r\n" + 
    			"FROM\r\n" + 
    			"	sfzs_czrk \r\n" + 
    			"WHERE\r\n" + 
    			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
    			"	AND size IN ( SELECT size FROM sfzs_czrk WHERE sfzs_czrk.YEAR = '"+date+"' AND fybm = '"+fybm+"' ) \r\n" + 
    			"	) AS c,\r\n" + 
    			"	(\r\n" + 
    			"SELECT\r\n" + 
    			"	rank \r\n" + 
    			"FROM\r\n" + 
    			"	(\r\n" + 
    			"SELECT\r\n" + 
    			"	fy,\r\n" + 
    			"	fybm,\r\n" + 
    			"	yearzs,\r\n" + 
    			"	rank ( ) over ( ORDER BY yearzs ) AS rank \r\n" + 
    			"FROM\r\n" + 
    			"	(\r\n" + 
    			"SELECT\r\n" + 
    			"	fyjc AS fy,\r\n" + 
    			"	fybm,\r\n" + 
    			"	( CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs \r\n" + 
    			"FROM\r\n" + 
    			"	sfzs_czrk\r\n" + 
    			"	LEFT JOIN (\r\n" + 
    			"SELECT\r\n" + 
    			"	jbfy,\r\n" + 
    			"	fyjc AS fy,\r\n" + 
    			"	sum( CASE WHEN sfzs_result_fzgj.date = '"+date+"' THEN result ELSE 0 END ) AS yearzs \r\n" + 
    			"FROM\r\n" + 
    			"	sfzs_result_fzgj,\r\n" + 
    			"	sfzs_czrk \r\n" + 
    			"WHERE\r\n" + 
    			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
    			"	AND sfzs_result_fzgj.jbfy = sfzs_czrk.fybm \r\n" + 
    			"	AND size IN ( SELECT size FROM sfzs_czrk WHERE sfzs_czrk.YEAR = '"+date+"' AND fybm = '"+fybm+"' ) \r\n" + 
    			"	AND type = '"+type+"' \r\n" + 
    			"	AND date IN ( '"+date+"', '"+(Integer.parseInt(date)-1)+"' ) \r\n" + 
    			"	AND kind = '中' \r\n" + 
    			"GROUP BY\r\n" + 
    			"	jbfy,\r\n" + 
    			"	fyjc \r\n" + 
    			"	) t ON sfzs_czrk.fybm = t.jbfy \r\n" + 
    			"WHERE\r\n" + 
    			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
    			"	AND size IN ( SELECT size FROM sfzs_czrk WHERE sfzs_czrk.YEAR = '"+date+"' AND fybm = '"+fybm+"' ) \r\n" + 
    			"ORDER BY\r\n" + 
    			"	yearzs,\r\n" + 
    			"	fy \r\n" + 
    			"	) t1 \r\n" + 
    			"	) t2 \r\n" + 
    			"WHERE\r\n" + 
    			"	fybm = '"+fybm+"' \r\n" + 
    			"	) AS rank,\r\n" + 
    			"	( SELECT fyjc FROM sfzs_czrk WHERE sfzs_czrk.YEAR = '"+date+"' AND fybm = '"+fybm.substring(0, 1)+"' || '00' ) AS xq,\r\n" + 
    			"	num,\r\n" + 
    			"	size \r\n" + 
    			"FROM\r\n" + 
    			"	sfzs_czrk \r\n" + 
    			"WHERE\r\n" + 
    			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
    			"	AND fybm = '"+fybm+"'";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    public void getGjljzsGy(Map dataMap,String kind,String type, String date, String fybm,String fymc) {//法官工作量变化指数
    	getSectionOne(dataMap,kind,type,date,fybm);
		List qgTableList = getQgTableList(type, date, false);
		List qgSelfList = getQgTableList(type, date, true);
		List qgTableList3 = new ArrayList();
		for(int i=qgTableList.size()-1;i>=0;i--) {//为全国报告增加
			Map row = (Map)qgTableList.get(i);
			String fy = row.get("fy").toString();
			if(fy.contains("北京")||fy.contains("天津")||fy.contains("上海")||fy.contains("重庆")) {
				continue;
			}else {
				dataMap.put("sfgl_gjlj_rank1_qg",NumUtil.toSheng(fy.toString())+"法院");
				if(fymc.equals("全国")) {
					List zjList = getZjTableList(type, date, row.get("jbfy").toString());
					Map rowzj = (Map)zjList.get(0);
					String rankZJ1 = rowzj.get("fy").toString();
					dataMap.put(31,NumUtil.toSheng(fy.toString()));
					dataMap.put("sfgl_gjlj_rank1_zj", rankZJ1);//中级排名第一
				}
				break;
			}
		}
		baseService.getGyDataIntoMap(dataMap, qgTableList, qgSelfList, qgTableList3, fymc, "sfgl_gjlj");
		List zjTableList = getZjTableList(type, date, fybm);
		List gylist = baseService.getSelfZs("sfzs_result_fzgj", type, date, fybm, "");
		baseService.getZyDetailDataIntoMap(dataMap, zjTableList, gylist, fymc, "sfgl_gjlj");
		//处理表格
		if(fymc.equals("全国")) {
			List fbList = getFbGjList(type,date,fybm);
			dataMap.put("array15",qgTableList);
			dataMap.put("array49",fbList);
		}else {
			List fbList = getFbZjList(type,date,fybm);
			dataMap.put("array29",qgTableList3);
			dataMap.put("array30",zjTableList);
			dataMap.put("array50",fbList);
		}
			
    } 
    public void getGjljzsZy(Map dataMap,String kind,String type, String date, String fybm,String fymc) {//裁判文书稳定指数
    	
		List fbList = getFbQxList(type,date,fybm);
		baseService.handleFb(fbList, fymc);
		List zjTableList = new ArrayList();
		List tdTableList = new ArrayList();
		List xqTableList = getXqTableList(type, date, fybm);
		List zylist = baseService.getSelfZs("sfzs_result_fzgj", type, date, fybm, "");
		baseService.getQxDetailDataIntoMap(dataMap, xqTableList, zylist, fymc, "sfgl_gjlj");
		if(fybm.startsWith("1")||fybm.startsWith("2")||fybm.startsWith("M")||fybm.startsWith("9")) {
			getSectionOne(dataMap,"辖",type,date,fybm);
			tdTableList = getTdZxTableList(type, date, fybm);
			baseService.getTdZxDataIntoMap(dataMap, tdTableList, fybm, "sfgl_gjlj");
			zjTableList = getZjZxTableList(type, date, fybm);
			baseService.getZyDataIntoMap(dataMap, zjTableList, fybm, "sfgl_gjlj");
			
			dataMap.put("array49",zjTableList);
			dataMap.put("array50",tdTableList);
			dataMap.put("array51",xqTableList);
			dataMap.put("array74",fbList); 
		}else {
			getSectionOne(dataMap,kind,type,date,fybm);
			List infoList = getQxInfo(date, fybm, type);
			tdTableList = getTdZjTableList(type, date, fybm);
			baseService.getTdCityDataIntoMap(dataMap, infoList, tdTableList, "sfgl_gjlj",fybm);
			zjTableList = getZjTableList(type, date, fybm);
			baseService.getZyDataIntoMap(dataMap, zjTableList, fybm, "sfgl_gjlj");
			// 同等收案量排行
			List tdSalTableList = getTdSalTableList(type, date, fybm);
			baseService.getTdSalDataIntoMap(dataMap, tdSalTableList, fymc,"sfgl_gjlj");	
			
			//处理表格
			dataMap.put("array53",zjTableList);
			dataMap.put("array54",tdTableList);
			dataMap.put("array55",xqTableList);
			dataMap.put("array79",fbList); 
		}
    }

    public void getQxZs(Map dataMap,String kind, String type, String date, String fybm) {//区县法院指数
    	getSectionOne(dataMap,kind,type,date,fybm);
    	List xqTableList = getXqTableList(type, date, fybm);
    	List gylist = baseService.getGyOrZyXqZs("sfzs_result_fzgj", type, date, fybm, "","高");
    	List zylist = baseService.getGyOrZyXqZs("sfzs_result_fzgj", type, date, fybm, "","中");
    	baseService.getQxDataIntoMap(dataMap, xqTableList, fybm, "sfgl_gjlj",gylist,zylist);
    	String ssxq_gy_rank = getRankInGyxq(type,date,fybm).get(0).toString();
    	dataMap.put("sfgl_gjlj_rank_gy",  "第" + NumUtil.toChineseNum(ssxq_gy_rank) + "位");
    	List fbList = getFbQxSelfList(type,date,fybm);
		for(int i=0;i<fbList.size();i++) {
			Map row = (Map)fbList.get(i);
			row.put("id", i+1);
			row.put("lv", NumUtil.toNxiaoshu(row.get("result").toString(), 2)+"%");
		}
    	dataMap.put("array25",xqTableList);
    	dataMap.put("array43",fbList); 
    }
    
    public void getSectionOne(Map dataMap,String kind,String type, String date, String fybm) {
    	List gslist = getGsList(kind,type,date,fybm);//获取指数概况
    	int gj = 0;
    	int fz = 0;
    	double zs = 0;
    	double zsLastYear = 0;   			
    	if(gslist.size()>0) {
    		Map map = (Map)gslist.get(0);
        	gj = Integer.parseInt(map.get("yeargj").toString());//干警数量
        	fz = Integer.parseInt(map.get("yearfz").toString());//犯罪案量	
        	zs = NumUtil.toNxiaoshu(map.get("yearzs").toString(), 2);   
        	zsLastYear = NumUtil.toNxiaoshu(map.get("lastyearzs").toString(), 2);   	
    	}
    	dataMap.put("sfgl_gjljzs", zs);
		dataMap.put("sfgl_gjljzs_lastyear", zsLastYear);
		if(gj==0) {
			if(kind.equals("高")) {
				fybm = fybm.substring(0, 1);
			}else if(kind.equals("中")) {
				fybm = fybm.substring(0, 2);
			}
			List gjLsit = getGj(fybm);
			if(gjLsit.size()>0) {
				Map row = (Map)gjLsit.get(0);
				gj = Integer.parseInt(row.get("gj").toString());
			}
		}
		dataMap.put("sfgl_gjlj_gj", NumUtil.toWan(gj));		
		dataMap.put("sfgl_gjlj_fz", fz);
		
		if(zs>zsLastYear) {
			dataMap.put("sfgl_gjlj_tb", "上升"+(NumUtil.toNxiaoshu(zs-zsLastYear,2)));//同比
		}else if(zs<zsLastYear) {
			dataMap.put("sfgl_gjlj_tb", "下降"+(NumUtil.toNxiaoshu(zsLastYear-zs,2)));//同比
		}else {
			dataMap.put("sfgl_gjlj_tb", "不变");//同比
		}
    }

}
